#!/usr/bin/env perl
#
#	Assemble components (package list, localised descriptions, group and system 
#	lists) into a single database, which is either created or upgraded
#
#	Stuart Crook, 21/3/09
#
use DBI;

sub usage {
	print "pdpdmake: Assemble components into a PureDarwin Package Database file\n";
	print "  pdpdmake { -n | -u } file [ -v num ] [ { -p | -l | -g | -s | -c } file ]\n";
	print "    -n file  Create a new database\n";
	print "    -u file  Update an existing database\n";
  print "    -v num   Set database version number\n";
	print "    -p file  Include a package list file (see dbaudit)\n";
	print "    -l file  Include a localised language file\n";
	print "    -g file  Include a package group file\n";
	print "    -s file  Include a system group file\n";
	print "    -c file  Include a core package file\n";
}

#
#	Add a package line to @packages, if it isn't there already
#
sub addpkg {
	#print "-> $_[0]\n";
	foreach $pkg (@packages) {
		if($pkg eq $_[0]) { return; }
	}
	push(packages, $_[0]);
}

#
#	Simple sql wrappers
#
#	Preform an SQL statement, no return
#
sub sql_do {
	my $sql = $db->prepare($_[0]);
	$sql->execute();
}

#
#	Preform an SQL statement, binding in parameters
#
sub sql_p {
	my $sql, $i;
	$sql = $db->prepare($_[0]);
	for( $i = 1; $i <= $#_; $i++ ) {
		$sql->bind_param($i, $_[$i]);
	}
	$sql->execute();
}

#
#	Find the id of the package named $_[0], or 0 otherwise
#
sub pkg_id {
	my $sql, $row;
	$sql = $db->prepare('SELECT id FROM packages WHERE name=?');
	$sql->bind_param(1, $_[0]);
	$sql->execute();
	if($row = $sql->fetch) {
		return $row->[0];
	}
	return -1;
}

#
#	Same for systems
#
sub sys_id {
	my $sql, $row;
	$sql = $db->prepare('SELECT id FROM systems WHERE name=?');
	$sql->bind_param(1, $_[0]);
	$sql->execute();
	if($row = $sql->fetch) {
		return $row->[0];
	}
	return -1;
}

#
#	And for groups
#
sub grp_id {
	my $sql, $row;
	$sql = $db->prepare('SELECT id FROM groups WHERE name=?');
	$sql->bind_param(1, $_[0]);
	$sql->execute();
	if($row = $sql->fetch) {
		return $row->[0];
	}
	return -1;
}

#
#	Write two values ($_[2], $_[4]) into field $_[1] and $_[2] of a glue
#	table ($_[0]), after checking that they aren't there already
#
sub sql_g {
	#print @_;
	my $sql = $db->prepare("SELECT * FROM $_[0] WHERE $_[1]=$_[2] AND $_[3]=$_[4]");
	$sql->execute();
	if(!($sql->fetch)) {
		$sql= $db->prepare("INSERT INTO $_[0] ($_[1],$_[3]) VALUES ($_[2],$_[4])");
		$sql->execute;
	}
}

#
#	Check to see if a description for package $_[0] in language $_[1] exists
#
sub desc_exists {
	my $sql = $db->prepare("SELECT * FROM package_descs WHERE package_id=$_[0] AND language='$_[1]'");
	$sql->execute();
	if($sql->fetch) {
		return 1;
	}
	return 0;
}

#
#	Go!
#
if($#ARGV < 1) {
	usage();
	exit();
}

$mode = -1; # create or update
$debug = 0;
$dbversion = -1;

for( $i = 0; $i <= $#ARGV; $i++ ) {

	if($ARGV[$i] eq '-d') {
		$debug = 1;
	} elsif($ARGV[$i] eq '-n') {
		#	set the path to the new database
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		$dbfile = $ARGV[$i];
		$mode = 0;
	}
	elsif($ARGV[$i] eq '-u') {
		#	set the path to the new existing database
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		$dbfile = $ARGV[$i];
		$mode = 1;
	}
	elsif($ARGV[$i] eq '-v') {
		#	add a package file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		$dbversion = $ARGV[$i];
	}
	elsif($ARGV[$i] eq '-p') {
		#	add a package file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		push(plist, $ARGV[$i]);
	}
	elsif($ARGV[$i] eq '-c') {
		#	add a core package file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		push(clist, $ARGV[$i]);
	}
	elsif($ARGV[$i] eq '-l') {
		#	add a language file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		push(llist, $ARGV[$i]);
	}
	elsif($ARGV[$i] eq '-g') {
		#	add a group file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		push(glist, $ARGV[$i]);
	}
	elsif($ARGV[$i] eq '-s') {
		#	add a system file to the list
		if($#ARGV == $i++) {
			usage();
			exit;
		}
		push(slist, $ARGV[$i]);
	}
	elsif($ARGV[$i] eq '-d') {
		$debug = 1;
	}

}

#
#	Check what we just got
#
#	The database
#
if($mode == -1) {
	printf "Error: Name a database to create [-n] or upgrade [-u]\n";
	usage();
	exit();
}

if(($mode == 0) && ($dbversion == -1)) { # new
	printf "Error: Please provide an initial database version number\n";
	exit();
}
 
if(($mode == 1) && !(-e $dbfile)) { # upgrade
	printf "Error: File '$dbfile' does not exist\n";
	exit();
}

$db = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

if($debug == 1) {
	$sqlite_version = $db->{sqlite_version};
	print "Connected to db '$dbfile', SQLite version $sqlite_version\n";
}

#
#	Check the other parameters
#
if(($mode == 0) && ($#plist == -1)) { # new
	printf "Error: Provide at least one package list\n";
	usage();
	exit();
}

if(($mode == 0) && ($#llist == -1)) {
	printf "Warning: No localised language files. Descriptions will be left blank\n";
}

if(($mode == 0) && ($#clist == -1)) {
	printf "Warning: No core package list\n";
}

if(($mode == 0) && ($#glist == -1)) {
	printf "Warning: No package group lists\n";
}

if(($mode == 0) && ($#slist == -1)) {
	printf "Warning: No system groups lists\n";
}

#
#	Load package names from the files from @plist and put in @packages
#
foreach $file (@plist) {
	if(open(TMP,$file)) {
		@temp = <TMP>;
		foreach $line (@temp) { addpkg($line); }
		undef(@temp);
		close(TMP);
	} else {
		printf "Error: Cannot open package list file '$file'\n";
	}
}

if(($mode == 0) && ($#packages == -1)) { # new
	print "Error: Packages files didn't include any package information\n";
	exit();
}

if($debug == 1) {
	$pcount = $#packages + 1;
	print "$pcount packages assembled\n";
}

#
#	If we are creating the database (-n), do it now
#
if($mode == 0) {
	if($debug == 1) { print "Creating database '$dbfile'\n"; }

	# the main tables
	#
	#	db_info: holds the database version, used for tracking updates
	#
	sql_do('CREATE TABLE db_info (version INTEGER)');
	#
	#	packages: contains information about the current version of the packages
	#
	sql_do('CREATE TABLE packages (id INTEGER PRIMARY KEY, name VARCHAR(32), available INTEGER, version VARCHAR(10), build VARCHAR(10), unres INTEGER, hash VARCHAR(40))');
	#
	sql_do('CREATE TABLE groups (id INTEGER PRIMARY KEY, name VARCHAR(64))');
	sql_do('CREATE TABLE systems (id INTEGER PRIMARY KEY, name VARCHAR(64))');
	sql_do('CREATE TABLE package_descs (package_id INTEGER, language VARCHAR(2), desc TEXT)');
	sql_do('CREATE TABLE group_descs (group_id INTEGER, language VARCHAR(2), desc TEXT)');
	sql_do('CREATE TABLE system_descs (system_id INTEGER, language VARCHAR(2), desc TEXT)');

	# the glue tables
	sql_do('CREATE TABLE deps (p1_id INTEGER, p2_id INTEGER)');
	sql_do('CREATE TABLE members (group_id INTEGER, package_id INTEGER)');
	sql_do('CREATE TABLE sys_packages (system_id INTEGER, package_id INTEGER)');
	sql_do('CREATE TABLE sys_groups (system_id INTEGER, group_id INTEGER)');

	# create the core group with id 0
	sql_do('INSERT INTO groups (id,name) VALUES (0,"core")');
}

#
#	Insert/update each package from @packages in the database, storing dependency
#	info in the %pdeps hash for latter
#
$pcount = 0;
foreach $pkg (@packages) {
	if($pkg =~ /^(\S+) ([YN]) ([\d.?]+)/) {	# valid dbaudit package list line?
	
		$pcount++;
		$name = $1;			# package name without version
		$version = $3;	# version

		#if($count > 10) { next; }

		if( $2 eq 'N' ) {	# package's binary root is missing

			if($debug == 1) { print "$name has a missing binary root\n"; }

			$available = 0;
			$build = '';
			$hash = '';
			$unres = 0;

		} else {	# we have dependencies information

			$' =~ /(\w+) (\w+) [?] { (.*) }/; # 'extract the values

			$available = 1;
			$build = $1;
			$hash = $2;

			# the '?' is depot info, which we aren't doing yet

			@deps = (sort split(' ', $3)); # sorts /unresolved first
			$dcount = ($#deps == -1) ? 0 : ($#deps+1);
			$unres = 0;

			if($debug == 1) { 
				print "$name (v$version) build $build, $dcount dependencies, hash $hash\n"; 
			}

			if($dcount != 0) {
				while($deps[0] =~ m|^/|) {
					shift @deps;
					$unres++;
				}				
				$pdeps{$name} = [@deps]; # thank you, Randal L. Schwartz
				@deps = ();
			}
		}

		# Are we updating and is package #name already in the database?
		if(($mode == 1) && (($id = pkg_id($name)) != -1)) {

			if($debug == 1) { print "updating packages row $id ($name)\n"; }

			sql_p('UPDATE packages SET name=?, available=?, version=?, build=?, unres=?, hash=? WHERE id=?', $name, $available,$version, $build, $unres, $hash, $id);

		} else { # insert new

			sql_p('INSERT INTO packages (name,available,version,build,unres,hash) VALUES (?,?,?,?,?,?)', $name, $available,$version, $build, $unres,$hash);

			if($debug == 1) { 
				#$id = $db->last_insert_id();
				$id = $db->func('last_insert_rowid');
				print "inserted package row $id ($name)\n";
			}

		}

	}
}

#
#	Set dependency information
#
#	Currently there is no way to remove a packages dependency via pd2make
#
foreach $name (keys %pdeps) {
	# check the package name is genuine
	if(($p1_id = pkg_id($name)) == -1) { next; }

	foreach $dep (@{$pdeps{$name}}) {
		if($dep =~ m|^/|) {

			print "Unresolved dependency $dep slipped through!\n";

		} else { # package dependency

			if(($p2_id = pkg_id($dep)) == -1) {

				print "Package $dep, a dependency of $name, is unknown\n";

			} elsif($p1_id == $p2_id) {
				if($debug == 1) { 
					printf "Caught self-dependency, $_[0] table, $_[1]=$_[3]=$_[2]\n";
				}
			} else { # could do a glue
				
				sql_g('deps', 'p1_id', $p1_id, 'p2_id', $p2_id);

			} 
		}
	} # foreach $dep
}

#
#	Add descriptions 
#
foreach $file (@llist) {
	if(open(TMP,$file)) {
		if($debug == 1) { 
			printf "Reading description file '$file'\n"; 
		}
		# determine what language this is
		if($file =~ /\.(\w\w)\.txt/) {
			$lang = $1;
			print "file $file is for language '$lang'\n";
			@temp = <TMP>;
			foreach $line (@temp) {
				chop($line);
				if($line =~ /^([^#\s]\S*) (.*)/) {
					if($debug == 1) {
						printf "Description for $1 is: $2\n";
					}
					if(($pid = pkg_id($1)) != -1) { 
						# package exists, but has it been described before?
						if(desc_exists($pid,$lang)) {
							sql_p('UPDATE package_descs SET desc=? WHERE package_id=? AND language=?', $2, $pid, $lang);
						} else {
							sql_p('INSERT INTO package_descs (package_id,language,desc) VALUES (?,?,?)', $pid, $lang, $2);
						}
					} else {
						if($debug == 1) {
							printf "Package '$1' unknown\n";
						}
					}
				}
			}
			undef(@temp);
		} else {
			printf "Error: Unable to determine target language of descriptions file '$file'\n";
		}
		close(TMP);
	} else {
		printf "Error: Cannot open core package list file '$file'\n";
	}
}

#
#	Set up membership of the core group (group_id==0)
#
foreach $file (@clist) {
	if(open(TMP,$file)) {
		if($debug == 1) { 
			printf "Reading core list file '$file'\n"; 
		}
		@temp = <TMP>;
		foreach $line (@temp) {
			chop($line);
			if(($line =~ /^([^#\s]\S*)/) && (($pid = pkg_id($1)) != -1)) {
				#printf "Adding package '$1' id $pid\n";
				sql_g('members','group_id',0,'package_id',$pid);
			} else {
				if($debug == 1) { 
					printf "Unknown core package '$1'\n"; 
				}
			}
		}
		undef(@temp);
		close(TMP);
	} else {
		printf "Error: Cannot open core package list file '$file'\n";
	}
}

#
#	Set up other groups
#
foreach $file (@glist) {
	if(open(TMP,$file)) {
		if($debug == 1) { 
			printf "Reading group list file '$file'\n"; 
		}
		# get the group's id, creating it if need be
		if($file =~ m|[/.]?(\w+).txt$|) {
			if(($gid = grp_id($1)) == -1) {
				# create a group called $1
				if($debug == 1) {
					printf "Creating new group '$1'\n";
				}
				sql_p('INSERT INTO groups (name) VALUES (?)', $1);
				$gid = $db->func('last_insert_rowid');
			}
			# now back to reading in the list of packages		
			@temp = <TMP>;
			foreach $line (@temp) { 
				chop($line);
				if(($line =~ /^([^#\s]\S*)/) && (($pid = pkg_id($1)) != -1)) {
					#printf "Adding package '$1' id $pid\n";
					sql_g('members','group_id',$gid,'package_id',$pid);
				} else {
					if($debug == 1) { 
						printf "Unknown package '$1' in group file '$file'\n"; 
					}
				}
			}
			undef(@temp);
		} else {
			printf "Could not get group name from file name '$file'\n";
		}
		close(TMP);
	} else {
		printf "Error: Cannot open group package list file '$file'\n";
	}
}

#
#	Set up systems
#
foreach $file (@slist) {
	if(open(TMP,$file)) {
		if($debug == 1) { 
			printf "Reading system list file '$file'\n"; 
		}
		# get the group's id, creating it if need be
		if($file =~ m|[/.]?(\w+).txt$|) {
			if(($sid = sys_id($1)) == -1) {
				# create a system called $1
				if($debug == 1) {
					printf "Creating new system '$1'\n";
				}
				sql_p('INSERT INTO systems (name) VALUES (?)', $1);
				$sid = $db->func('last_insert_rowid');
			}
			# now back to reading in the list of groups and packages		
			@temp = <TMP>;
			foreach $line (@temp) { 
				chop($line);
				if($line =~ /^([^#\s]\S*)/) {
					#
					#	$1 could be either a group or a package
					#
					if(($gid = grp_id($1)) != -1) { # it's a group
						
						sql_g('sys_groups','system_id',$sid,'group_id',$gid);				

					} elsif(($pid = pkg_id($1)) != -1) { # it's a package
						#printf "Adding package '$1' id $pid\n";
						sql_g('sys_packages','system_id',$sid,'package_id',$pid);

					} else {
						if($debug == 1) { 
							printf "Unknown package or group '$1' in system file '$file'\n"; 
						}
					}
				}
			}
			undef(@temp);
		} else {
			printf "Could not get system name from file name '$file'\n";
		}
		close(TMP);
	} else {
		printf "Error: Cannot open system package list file '$file'\n";
	}
}


#
#	Set the database version number
#
if($mode == 0) {
	sql_p("INSERT INTO db_info (version) VALUES (?)", $dbversion);
} else {
	if($version == -1) {
		# look up current version num
		$sql = $db->prepare('SELECT version FROM db_info');
		$sql->execute();
		if($row = $sql->fetch) {
			$dbversion = $row[0] + 1;
		} else {
			$dbversion = 1;
		}
	}
	sql_p("UPDATE db_info SET version=?", $dbversion);
}

print "Created pdpd version $dbversion\n";

